﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class sqlData
    {
        public static SqlConnection TaoKetNoi()
        {
            SqlConnection cnn = new SqlConnection(@"Data Source=TRANTHANH-PC\SQLEXPRESS;Initial Catalog=BanSach;Integrated Security=True");
            return cnn;
        }
    }
    public class Nhap_DAO
    {
        //load danh sach HD:
        public static DataTable LoadDSHDNhap()
        {
            SqlConnection cnn = sqlData.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("SelectAll_HDNhap", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Them HD Moi:
        public static void ThemHDNhap(Nhap_DTO hd)
        {
            SqlConnection cnn = sqlData.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("insert_HDNhap", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maHD", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@masach", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@ngaynhap", SqlDbType.Date);
            cmd.Parameters.Add("@SLNhap", SqlDbType.Int);
            cmd.Parameters.Add("@GiaNhap", SqlDbType.Float);
            cmd.Parameters.Add("@ChietKhau", SqlDbType.NChar,10);
            cmd.Parameters.Add("@GhiChu", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@MaNV", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@MaNXB", SqlDbType.NChar, 10);
            //gan:
            cmd.Parameters["@maHD"].Value = hd.MaHD;
            cmd.Parameters["@masach"].Value = hd.MaSach;
            cmd.Parameters["@ngaynhap"].Value = hd.NgayNhap;
            cmd.Parameters["@SLNhap"].Value = hd.SLNhap;
            cmd.Parameters["@GiaNhap"].Value = hd.GiaNhap;
            cmd.Parameters["@ChietKhau"].Value = hd.ChietKhau;
            cmd.Parameters["@GhiChu"].Value = hd.GhiChu;
            cmd.Parameters["@MaNV"].Value = hd.MaMaNV;
            cmd.Parameters["@MaNXB"].Value = hd.MaNXB;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        
        //Sua Hd:
        public static void SuaHD(Nhap_DTO sua)
        {
            SqlConnection cnn = sqlData.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("Update_HDNhap", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maHD", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@masach", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@ngaynhap", SqlDbType.Date);
            cmd.Parameters.Add("@SLNhap", SqlDbType.Int);
            cmd.Parameters.Add("@GiaNhap", SqlDbType.Float);
            cmd.Parameters.Add("@ChietKhau", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@GhiChu", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@MaNV", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@MaNXB", SqlDbType.NChar, 10);
            //gan:
            cmd.Parameters["@maHD"].Value = sua.MaHD;
            cmd.Parameters["@masach"].Value = sua.MaSach;
            cmd.Parameters["@ngaynhap"].Value = sua.NgayNhap;
            cmd.Parameters["@SLNhap"].Value = sua.SLNhap;
            cmd.Parameters["@GiaNhap"].Value = sua.GiaNhap;
            cmd.Parameters["@ChietKhau"].Value = sua.ChietKhau;
            cmd.Parameters["@GhiChu"].Value = sua.GhiChu;
            cmd.Parameters["@MaNV"].Value = sua.MaMaNV;
            cmd.Parameters["@MaNXB"].Value = sua.MaNXB;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //Xoa HD:
        public static void XoaHD(string mahd)
        {
            SqlConnection cnn = sqlData.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("Delete_HDNhap", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaHDNhap", SqlDbType.NChar, 10);
            cmd.Parameters["@MaHDNhap"].Value = mahd;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //Tim HD Nhap Theo MaHDNhap:
        public static DataTable TimMaHDNhap(string mahd)
        {
            SqlConnection cnn = sqlData.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("TimtheoMaHDNhap", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = mahd;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Tim Theo Ma Sach:
        public static DataTable TimMaSach(string masach)
        {
            SqlConnection cnn = sqlData.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("TimtheoMaSach", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = masach;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Tim theo ma NV:
        public static DataTable TimMaNV(string manv)
        {
            SqlConnection cnn = sqlData.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("TimMaNV", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = manv;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Tim MaNXB:
        public static DataTable TimMaNXB(string manxb)
        {
            SqlConnection cnn = sqlData.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("TimMaNXB", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = manxb;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Thong Ke Theo Ma HD:
        public static DataTable TongMaHD(string mahd)
        {
            SqlConnection cnn = sqlData.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("tiennhaptheomaHD", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maHD", SqlDbType.NChar, 10);
            cmd.Parameters["@maHD"].Value = mahd;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Thong Ke Theo ngay :
        public static DataTable TongTienTheoNgay(DateTime ngay)
        {
            SqlConnection cnn = sqlData.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("tiennhaptheongay", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ngay", SqlDbType.Date);
            cmd.Parameters["@ngay"].Value = ngay;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Thong Ke Theo NhanVien :
        public static DataTable TongTienTheoNhanVien(string ten)
        {
            SqlConnection cnn = sqlData.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("tiennhaptheoNV", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ten", SqlDbType.NVarChar, 50);
            cmd.Parameters["@ten"].Value = ten;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }



    }
}
